﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;


namespace DAL.BusinessDeal
{
    public class StSpProcessCTNAmountDAL
    {
        private const string selectProcessVoyageDAL = null;

        private const string selectVoyageByVOY_ID = @"select va.*,cs.csz_csizenm ,rc.rv_meaning
from st_voyage_amounts  va 
left join st_container_sizes  cs on va.VAT_CSZ_CSIZECD= cs.csz_csizecd
left join (select rv_domain,rv_low_value,rv_meaning from st_ref_codes where rv_domain='OVLMTCD' )  rc on va.VAT_OVLMTCD= rc.rv_low_value
 {0}";

        /// <summary>
        /// Get DataTable
        /// </summary>
        /// <param name="textWhere">conditon</param>
        /// <param name="orderText">sort</param>
        /// <returns></returns>
        public DataTable GetTable()
        {
            return GetDataTable(selectProcessVoyageDAL);
        }

        /// <summary>
        /// 得到航次箱量
        /// </summary>
        /// <param name="textWhere"></param>
        /// <param name="orderText"></param>
        /// <returns></returns>
        public DataTable GetTable(string textWhere, string orderText)
        {
            string query = string.Empty;
            if (!string.IsNullOrEmpty(textWhere))
                query += " where " + textWhere;
            if (!string.IsNullOrEmpty(orderText))
                query += " order by " + orderText;

            return GetDataTable(string.Format(selectVoyageByVOY_ID, query));
        }

        /// <summary>
        /// Get DataTable
        /// </summary>
        /// <param name="sql">SQL statement</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            DataTable dt = db.ExecuteDataTable(dbCommand);
            if (dt != null)
            {
                dt.TableName = "SP_Process_CTNAmount";
            }
            return dt;
        }

        /// <summary>
        /// 新增 修改 数据
        /// </summary>
        /// <param name="P_Operation_Code"></param>
        /// <param name="P_VOY_ID"></param>
        /// <param name="P_User_ID"></param>
        /// <param name="P_VOY_IEFG"></param>
        /// <param name="P_VOY_SLN_ID"></param>
        /// <param name="P_SCD_ID"></param>
        /// <param name="P_VOY_FLT_ID"></param>
        /// <param name="P_VOY_VOYAGE"></param>
        /// <param name="P_VOY_INTRADE"></param>
        /// <returns></returns>
        public string InsertUpdateProcessCTNAmount(int P_Operation_Type, string P_VOY_ID, string P_CSZ_CSIZECD,
           string P_STS_CSTATUSCD, string P_DNGFG, string P_OVLMTCD, string P_RFFG, string P_AMOUNT, string P_USER_ID
            , string P_EFFG, string P_VAT_ID, string P_SCD_ID)
        {

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("web_sp_process_ctnamount");
            db.AddInParameter(dbCommand, "P_Operation_Type", DbType.Int32, P_Operation_Type);
            db.AddInParameter(dbCommand, "P_VOY_ID", DbType.String, P_VOY_ID);
            db.AddInParameter(dbCommand, "P_CSZ_CSIZECD", DbType.String, P_CSZ_CSIZECD);
            db.AddInParameter(dbCommand, "P_STS_CSTATUSCD", DbType.String, P_STS_CSTATUSCD);
            db.AddInParameter(dbCommand, "P_DNGFG", DbType.String, P_DNGFG);
            db.AddInParameter(dbCommand, "P_OVLMTCD", DbType.String, P_OVLMTCD);
            db.AddInParameter(dbCommand, "P_RFFG", DbType.String, P_RFFG);
            db.AddInParameter(dbCommand, "P_AMOUNT", DbType.String, P_AMOUNT);
            db.AddInParameter(dbCommand, "P_USER_ID", DbType.String, P_USER_ID);
            db.AddInParameter(dbCommand, "P_EFFG", DbType.String, P_EFFG);
            db.AddInParameter(dbCommand, "P_VAT_ID", DbType.String, P_VAT_ID);
            db.AddInParameter(dbCommand, "P_SCD_ID", DbType.String, P_SCD_ID);
            db.AddOutParameter(dbCommand, "ReturnValue", DbType.String, 1000);
            dbCommand.Parameters["ReturnValue"].Direction = ParameterDirection.Output;
            db.ExecuteNonQuery(dbCommand);
            return dbCommand.Parameters["ReturnValue"].Value.ToString();

        }

    }
}
